package com.samsung.common.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class BaseExcelUtil {
	//显示的导出表的标题  
    private String title;  
    //导出表的列名  
    private String[] rowName;
    //导出excel的文件名称
    private String fileName;
    //导出的数据集
    private List<List<Object>>  dataList;
    
    HttpServletRequest request;
    
    private Workbook workbook;
    
    private Sheet sheet;
    
	public BaseExcelUtil(String title, String[] rowName, String fileName, List<List<Object>> dataList,
			HttpServletRequest request) {
		super();
		this.title = title;
		this.rowName = rowName;
		this.fileName = fileName;
		this.dataList = dataList;
		this.request = request;
	}
	
	public String export() throws IOException{
		this.beforeExport();
		this.exportData();
		String filePath = this.makeFile();
		return filePath;
	}

	private void beforeExport() {
		// 创建工作簿对象
		workbook = new XSSFWorkbook();
		// 创建工作表
		sheet = workbook.createSheet(title);
		// 产生表格标题行
		Row rowm = sheet.createRow(0);
		Cell cellTiltle = rowm.createCell(0);

		// sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
		// 获取列头样式对象
		CellRangeAddress cellRangeAddress = new CellRangeAddress((short) 0, (short) 1, (short) 0, (short) (rowName.length - 1));
		sheet.addMergedRegion(cellRangeAddress);
		this.setRegionBorder(cellRangeAddress, sheet, workbook);
		CellStyle columnTopStyle = this.getColumnTopStyle(workbook);
		cellTiltle.setCellValue(title);
		cellTiltle.setCellStyle(columnTopStyle);

		// 定义所需列数
		int columnNum = rowName.length;
		// 在索引2的位置创建行(最顶端的行开始的第二行)
		Row rowRowName = sheet.createRow(2);

		// 将列头设置到sheet的单元格中
		for (int n = 0; n < columnNum; n++) {
			// 创建列头对应个数的单元格
			Cell cellRowName = rowRowName.createCell(n);
			// 设置列头单元格的数据类型
			cellRowName.setCellType(Cell.CELL_TYPE_STRING);
			XSSFRichTextString text = new XSSFRichTextString(rowName[n]);
			// 设置列头单元格的值
			cellRowName.setCellValue(text);
			// 设置列头单元格样式
			cellRowName.setCellStyle(columnTopStyle);
		}
	}
    
	private void exportData() {
		// 单元格样式对象
		CellStyle style = this.getStyle(workbook);
		// 将查询出的数据设置到sheet对应的单元格中
		for (int i = 0; i < dataList.size(); i++) {
			// 遍历每个对象
			List<Object> list = dataList.get(i);
			// 创建所需的行数
			Row row = sheet.createRow(i + 3);
			for (int j = 0; j < list.size(); j++) {
				Cell cell = null;
				cell = row.createCell(j, Cell.CELL_TYPE_STRING);
				cell.setCellValue(list.get(j) == null ? "":list.get(j).toString()); // 设置单元格的值
				cell.setCellStyle(style);
			}
		}
		// 定义所需列数
		int columnNum = rowName.length;
		// 让列宽随着导出的列长自动适应
		for (int colNum = 0; colNum < columnNum; colNum++) {
			int columnWidth = sheet.getColumnWidth(colNum) / 256;
			for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Row currentRow = sheet.getRow(rowNum);
				// 当前行未被使用过
				if (currentRow.getCell(colNum) != null) {
					Cell currentCell = currentRow.getCell(colNum);
					if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
						int length = currentCell.getStringCellValue().getBytes().length;
						if (columnWidth < length) {
							columnWidth = length;
						}
					}
				}
			}
//			if (colNum == 0) {
//				sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
//			} else {
//				sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
//			}
			sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
		}
	}
	private String makeFile() throws IOException {
		String path = request.getSession().getServletContext().getRealPath("/");
		SimpleDateFormat format = new SimpleDateFormat("YYYYMMddHHmmss");
		String stamp = format.format(new Date());
		String subPath = path + "attachment/";
		int delCount = this.deleteTempFile(subPath);
		System.out.println(delCount);
		File file = new File(subPath);
		if (!file.exists()) {
			file.mkdirs();
		}
		String filePath = subPath+fileName+"_"+stamp+".xlsx";
		if (workbook != null) {
			FileOutputStream out = null;
			try {
				out = new FileOutputStream(new File(filePath));
				workbook.write(out);
			} catch (IOException e) {
				throw new IOException();
			} finally {
				try {
					if (out != null) {
						out.close();
					}
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}else{
			throw new NullPointerException("workbook 为空");
		}
		String webPath = filePath.substring(filePath.indexOf("attachment"));
		return "/"+webPath;
	}
	/*  
     * 列头单元格样式 
     */      
	private CellStyle getColumnTopStyle(Workbook workbook) {
		// 设置字体
		Font font = workbook.createFont();
		// 设置字体大小
		font.setFontHeightInPoints((short) 12);
		// 字体加粗
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		// 设置字体名字
		font.setFontName("Consolas");
		// 设置样式;
		CellStyle style = workbook.createCellStyle();
		// 设置底边框;
		style.setBorderBottom(CellStyle.BORDER_THIN);
		// 设置底边框颜色;
		style.setBottomBorderColor(IndexedColors.BLACK.index);
		// 设置左边框;
		style.setBorderLeft(CellStyle.BORDER_THIN);
		// 设置左边框颜色;
		style.setLeftBorderColor(IndexedColors.BLACK.index);
		// 设置右边框;
		style.setBorderRight(CellStyle.BORDER_THIN);
		// 设置右边框颜色;
		style.setRightBorderColor(IndexedColors.BLACK.index);
		// 设置顶边框;
		style.setBorderTop(CellStyle.BORDER_THIN);
		// 设置顶边框颜色;
		style.setTopBorderColor(IndexedColors.BLACK.index);
		// 在样式用应用设置的字体;
		style.setFont(font);
		// 设置自动换行;
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐;
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return style;
	}

	/*
	 * 列数据信息单元格样式
	 */
	private CellStyle getStyle(Workbook workbook) {
		// 设置字体
		Font font = workbook.createFont();
		// 设置字体大小
		// font.setFontHeightInPoints((short)10);
		// 字体加粗
		// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 设置字体名字
		font.setFontName("Consolas");
		// 设置样式;
		CellStyle style = workbook.createCellStyle();
		// 设置底边框;
		style.setBorderBottom(CellStyle.BORDER_THIN);
		// 设置底边框颜色;
		style.setBottomBorderColor(IndexedColors.BLACK.index);
		// 设置左边框;
		style.setBorderLeft(CellStyle.BORDER_THIN);
		// 设置左边框颜色;
		style.setLeftBorderColor(IndexedColors.BLACK.index);
		// 设置右边框;
		style.setBorderRight(CellStyle.BORDER_THIN);
		// 设置右边框颜色;
		style.setRightBorderColor(IndexedColors.BLACK.index);
		// 设置顶边框;
		style.setBorderTop(CellStyle.BORDER_THIN);
		// 设置顶边框颜色;
		style.setTopBorderColor(IndexedColors.BLACK.index);
		// 在样式用应用设置的字体;
		style.setFont(font);
		// 设置自动换行;
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐;
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return style;
	}
	
	private void setRegionBorder(CellRangeAddress region, Sheet sheet,Workbook wb){  
        RegionUtil.setBorderBottom(CellStyle.BORDER_THIN,region, sheet, wb);  
        RegionUtil.setBorderLeft(CellStyle.BORDER_THIN,region, sheet, wb);  
        RegionUtil.setBorderRight(CellStyle.BORDER_THIN,region, sheet, wb);  
        RegionUtil.setBorderTop(CellStyle.BORDER_THIN,region, sheet, wb);  
      
    }
	
	private int deleteTempFile(String filePath){
		File file = new File(filePath);
		if(!file.exists()) {
			file.mkdir();
		}
		String[] files = file.list();
		File tempFile = null;
		int count = 0;
		SimpleDateFormat format = new SimpleDateFormat("YYYYMMddHHmmss");
		for (String fString : files) {
			String name = fString.substring(fString.lastIndexOf("_")+1, fString.lastIndexOf("."));
			try {
				String currentDate = format.format(new Date());
				long time1 = format.parse(currentDate).getTime();
				long time2 = format.parse(name).getTime();
				if ((time1 - time2)/1000/60 >30) {//大于30分钟
					tempFile = new File(filePath+fString);
					if (tempFile.delete()) {
						count ++;
					}
				}
			} catch (ParseException e) {
				e.printStackTrace();
			}
			
		}
		return count;
	}
}
